<?

include 'functions.php';
include 'constants.php';

$action = $_GET['action'];

switch($action){
	case 'newpublickey':
		newpublickey();
		break;
	case 'update':
		update();
		break;
	case 'submit':
		submit();
		break;
	case 'entry':
		new_entry();
		break;
	case 'license':
		license();
		break;
	case 'getproducts':
		getproducts();
		break;
	case 'getprice':
		getprice();
		break;
	default:
		break;
}

function getprice(){
	$db=new SQLiteDatabase("db/mandi");
	$timecutoff = time()-(24*60*60);
	$result=$db->query("select rate_per_quintal, sold_timestamp from sale_table WHERE sold_timestamp > ".($timecutoff)." AND produce_id = ".$_GET['id']." ORDER BY sold_timestamp");
	$rows = array();
	$high;
	$low;
	$x_limit;
	$data = array();
	while($row=$result->fetch(SQLITE_ASSOC)){
		//{ 'rate_per_quintal' => value, 'sold_timestamp'=>value }
		$time_diff = ($row['sold_timestamp'] - $timecutoff);
		$data[] = array('rate'=>floatval($row['rate_per_quintal']), 'timediff'=> $time_diff);
		if($high == null || $high < floatval($row['rate_per_quintal'])){
			$high = floatval($row['rate_per_quintal']);
		}
		if($low == null || $low > floatval($row['rate_per_quintal'])){
			$low = floatval($row['rate_per_quintal']);
		}
		if($x_limit == null || $x_limit < $time_diff){
			$x_limit = $time_diff;
		}
	}
	
	echo json_encode(array('data'=>$data,'high'=>$high, 'low'=>$low, 'x_limit'=>$x_limit));
}

function license(){
	$db=new SQLiteDatabase("db/mandi");
	$id = $_GET['id'];
	$type = $_GET['type'];
	$query = 'select * from licenses where license_'.$type.' = '.$id;
	$result = $db->query($query);
	$result = $result->fetch(SQLITE_ASSOC);
	echo json_encode($result);
}

function getproducts(){
	$db=new SQLiteDatabase("db/mandi");
	$query = 'select id, name_en from product_table';
	$result = $db->query($query);
	$products = array();
	while($row=$result->fetch(SQLITE_ASSOC)){
		$products[$row['name_en']] = $row['id'];
	}
	echo json_encode($products);
}

function new_entry(){
	$employee = urldecode($_POST['employee']);
	$db=new SQLiteDatabase("db/mandi");
	
	if(is_valid_employee($db, $employee)){
		
	} else {
		echo json_encode('invalid employee id');
	}
	
/*
	employee_number     	INTEGER,
	id					VARCHAR(255) PRIMARY KEY,
	timestamp			NUMERIC,
	driver_name			VARCHAR(255),
	vehicle_number		INTEGER,
	vehicle_type		VARCHAR(255),
	product_id			REFERENCES product_table(id),
	product_weight		REAL,
	product_farm		VARCHAR(255),
	bearer_license		INTEGER, 
	comm_agent_license	INTEGER,
	fees*/
}

function submit(){
	$employee = intval(urldecode($_POST['employee']));
	$db=new SQLiteDatabase("db/mandi");
	
	if(is_valid_employee($db, $employee)){
		$employee = sanitize($employee);
		$sold = sanitize(urldecode($_POST['sold']));
		$entry_id = sanitize(urldecode($_POST['entry_id']));
		$toll = sanitize(urldecode($_POST['toll']));
		$seller_license = sanitize(urldecode($_POST['seller_license']));
		$buyer_license = sanitize(urldecode($_POST['buyer_license']));
		$produce = sanitize(urldecode($_POST['produce_id']));
		$weight = sanitize(urldecode($_POST['weight']));
		$rate = sanitize(urldecode($_POST['rate']));
		
		$total = $weight*$rate;
		
		$unload = FEES_UNLOAD*$weight;
		$clean = FEES_CLEAN*$weight;
		$weigh = FEES_WEIGH*$weight;
		$load = FEES_LOAD*$weight;
		$stitch = FEES_STITCH*$weight;
		$pack = FEES_PACK*$weight;

		$comm_mandi = $total*COMMISSION_MANDI;
		$comm_comm_agent = $total*COMMISSION_COMM_AGENT;
		
		$error_msg;
		if(@$db->queryExec(sanitize('INSERT INTO sale_table VALUES ('.
		'"'.MANDI_NAME.'", '.$employee.', '.$sold.', NULL, "'.$entry_id.'", '.
		$toll.', '.$seller_license.', '.$buyer_license.', '.$produce.', '.$weight.', '.
		$rate.', '.$total.', '.$unload.', '.$clean.', '.$weigh.', '.$load.', '.$stitch.', '.
		$pack.', '.$comm_comm_agent.', '.$comm_mandi.');'), $error_msg)){
			//success!
			$result = $db->query(sanitize('select ROWID from sale_table where entry_id = "'.$entry_id.
			'" and sold_timestamp = '.$sold));
			if($result){
				$row=$result->fetch(SQLITE_ASSOC);
				$id = $row['ROWID'];
				echo $id;
			}
		} else {
			echo json_encode($error_msg);
		}
	} else {
		echo json_encode('invalid employee id');
	}
}

function update(){
	$lastupdate = sanitize(intval($_GET['lastupdate']));
	$employee_id = sanitize(intval($_GET['id']));
	
	$db=new SQLiteDatabase("db/mandi");

	if($key = is_valid_employee($db, $employee_id)){
		
		$entries = array();
		$result=$db->query("select * from entry_table where timestamp > ".$lastupdate);
		if($result){
			while($row=$result->fetch(SQLITE_ASSOC)){
				$entries[] = $row;
			}
		}
		
		$licenses = array();
		$result=$db->query("select l.* from licenses as l, entry_table as e where".
			" l.license_seller = e.bearer_license AND e.timestamp > ".$lastupdate);
		if($result){
			while($row=$result->fetch(SQLITE_ASSOC)){
				$licenses[] = $row;
			}
		}
		
		$products = array();
		$result=$db->query("select * from product_table where created_at > ".$lastupdate);
		if($result){
			while($row=$result->fetch(SQLITE_ASSOC)){
				$products[] = $row;
			}
		}
		
		$result=$db->query('select name from employees where id = '.$employee_id);
		$row = $result->fetch(SQLITE_ASSOC);
		$employee_name = $row['name'];
		
		$settings = array(
			'name'=>MANDI_NAME, 
			'commission_mandi'=>COMMISSION_MANDI, 
			'commission_agent'=>COMMISSION_COMM_AGENT,
			'fee_unload'=>FEES_UNLOAD,
			'fee_clean'=> FEES_CLEAN,
			'fee_weigh'=>FEES_WEIGH,
			'fee_stitch'=>FEES_STITCH,
			'fee_load'=>FEES_LOAD,
			'fee_pack'=>FEES_PACK,
			'employee_name'=>$employee_name,
		);
		
		$return = array(
			'entries'=>$entries,
			'licenses'=>$licenses,
			'products'=>$products,
			'settings'=>$settings,
			'updated'=>time()
		);
		
		echo json_encode($return);
	} else {
		echo json_encode('invalid employee id');
	}	
}

function newpublickey(){

	$db=new SQLiteDatabase("db/mandi");

	$key = sanitize(urldecode($_POST['key']));
	$name = sanitize(urldecode($_POST['name']));
	$addr_st = sanitize(urldecode($_POST['addr_street']));
	$addr_state = sanitize(urldecode($_POST['addr_state']));
	$addr_pin = sanitize(urldecode($_POST['addr_pincode']));
	
	$query = "INSERT INTO employees (name, addr_street, addr_state, addr_pincode, public_key, status, created_at) VALUES (\"".$name."\",\"".$addr_st."\",\"".$addr_state."\",\"".$addr_pin."\", \"".$key."\", \"PENDING\", strftime('%s','now'));";

	if(@$db->queryExec($query, $error_msg)){
		$query = "SELECT id FROM employees WHERE public_key = \"".$key."\"";
		$result = $db->query($query, $error_msg);
		if($result){
			$row=$result->fetch(SQLITE_ASSOC);
			echo json_encode(array('id'=>$row['id'], 'key'=>'webservice public key!'));
		}else{
			echo json_encode($error_msg);
		}
	} else {
		echo json_encode($error_msg);
	}
}

?>